package sopia.conn;

import java.text.SimpleDateFormat;
import java.util.Date;

import sopia.cmm.CommonFunct;
import sopia.cmm.Constant;
import sopia.ctrl.ItemExpense;
import sopia.sg.G001Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class ItemExpenseAdapter {
	 /** Called when the activity is first created. */
	private DBAdapter dbAdapter ;
	private SQLiteDatabase db;  

	public ItemExpenseAdapter(Context context){
		  dbAdapter = new DBAdapter(context);
		  db = dbAdapter.getSqlDB();
	}
	
	public void beginTransaction(){
		db.beginTransaction();
	}
	public void setTransactionSuccessful(){
		db.setTransactionSuccessful();
	}
	public void endTransaction(){
		db.endTransaction();
	}
	
    public void close() 
    {
    	dbAdapter.close();
    }
    
    public long insertItemExpense(String itemName,
			String typeExpense, String amountItemExp, String dateItemExp,
			String commentItem, String accountName) 
    {
    	SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); 
    	Date date = new Date(dateItemExp);
    	
        ContentValues initialValues = new ContentValues();
        initialValues.put(SQLQuery.KEY_NAME_ITEMEXP, itemName);
        initialValues.put(SQLQuery.KEY_TYPENAME_ITEMEXP, typeExpense);
        initialValues.put(SQLQuery.KEY_AMOUNT_ITEMEXP, amountItemExp);
        initialValues.put(SQLQuery.KEY_DATE_ITEMEXP, dateFormat.format(date));
        initialValues.put(SQLQuery.KEY_COMMENT_ITEMEXP, commentItem);
        initialValues.put(SQLQuery.KEY_NAMEACCOUNT_ITEMEXP, accountName);
        return db.insert(SQLQuery.DB_ITEM_EXPENSE_TABLE, null, initialValues);
    }
    public String getAmountByWeek(String firstDate, String endDate) throws SQLException 
    {
    	SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); 
    	Date dateFrom = new Date(firstDate);
    	Date dateTo = new Date(endDate);
    	String strAmount = Constant.ZERO_VALUE;
    	Cursor mCursor = db.query(SQLQuery.DB_ITEM_EXPENSE_TABLE, new String[] {
        		CommonFunct.join("sum(", SQLQuery.KEY_AMOUNT_ITEMEXP,
        				") as ",SQLQuery.KEY_AMOUNT_ITEMEXP)}, 
                CommonFunct.join(SQLQuery.KEY_DATE_ITEMEXP,
                		" >= date('",
                		dateFormat.format(dateFrom),
                		"') AND ",
                		SQLQuery.KEY_DATE_ITEMEXP,
                		" <= date('",
                		dateFormat.format(dateTo),
                		"') AND ",
                		SQLQuery.KEY_NAMEACCOUNT_ITEMEXP,
                		" = '",
                		G001Activity.curNameAccount,
                		"'"),
                null, 
                null, 
                null, 
                null);
    	  if (mCursor != null) {
          	if (mCursor.moveToFirst()) {
          		strAmount = String.valueOf(mCursor.getDouble(0));
  		      }
  		      if (mCursor != null && !mCursor.isClosed()) {
  		    	  mCursor.close();
  		      }
          }
          return strAmount;
    }
    public String getAmountSpent() throws SQLException 
    {
    	String strAmount = Constant.ZERO_VALUE;
    	Cursor mCursor = db.query(SQLQuery.DB_ITEM_EXPENSE_TABLE, new String[] {
        		CommonFunct.join("sum(", SQLQuery.KEY_AMOUNT_ITEMEXP,
        				") as ",SQLQuery.KEY_AMOUNT_ITEMEXP)}, 
                CommonFunct.join(SQLQuery.KEY_NAMEACCOUNT_ITEMEXP,
                		" = '", G001Activity.curNameAccount,
                		"' "),
                null, 
                null, 
                null, 
                null);
    	  if (mCursor != null) {
          	if (mCursor.moveToFirst()) {
          		strAmount = String.valueOf(mCursor.getDouble(0));
  		      }
  		      if (mCursor != null && !mCursor.isClosed()) {
  		    	  mCursor.close();
  		      }
          }
          return strAmount;
    }
    //---deletes a particular account---
    public boolean deleteItemExpense(ItemExpense itemExp) 
    {
        return db.delete(SQLQuery.DB_ITEM_EXPENSE_TABLE, 
        		CommonFunct.join( SQLQuery.KEY_NAME_ITEMEXP," = '", itemExp.getItem(),"' AND ",
        		SQLQuery.KEY_TYPENAME_ITEMEXP," = '", itemExp.getType(),"' AND ",
        		SQLQuery.KEY_AMOUNT_ITEMEXP," = '", itemExp.getAmount(),"' AND ",
        		SQLQuery.KEY_DATE_ITEMEXP," = '", itemExp.getDate(),"' AND ",
        		SQLQuery.KEY_COMMENT_ITEMEXP," = '", itemExp.getComment(),"' AND ",
        		SQLQuery.KEY_NAMEACCOUNT_ITEMEXP," = '", itemExp.getAccount(),"'"),
        		null) > 0;
    }

    //---retrieves all the Item Expense---
    public Cursor getAllItemExpense() 
    {
        return db.query(SQLQuery.DB_ITEM_EXPENSE_TABLE, new String[] {
        		SQLQuery.KEY_NAME_ITEMEXP,
        		SQLQuery.KEY_TYPENAME_ITEMEXP,
        		SQLQuery.KEY_AMOUNT_ITEMEXP,
        		SQLQuery.KEY_DATE_ITEMEXP,
        		SQLQuery.KEY_COMMENT_ITEMEXP,
        		SQLQuery.KEY_NAMEACCOUNT_ITEMEXP}, 
                null, 
                null, 
                null, 
                null, 
                CommonFunct.join(SQLQuery.KEY_NAMEACCOUNT_ITEMEXP, " asc,",
                				SQLQuery.KEY_DATE_ITEMEXP, " desc,",
                				SQLQuery.KEY_TYPENAME_ITEMEXP, " desc"));
    }
    public Cursor getAmountByTypeExpenseAccount(String typeExpense, String nameAccount) throws SQLException 
    {
    	return db.query(SQLQuery.DB_ITEM_EXPENSE_TABLE, new String[] {
        		SQLQuery.KEY_AMOUNT_ITEMEXP}, 
                CommonFunct.join(SQLQuery.KEY_NAMEACCOUNT_ITEMEXP,
                		" = '",
                		nameAccount,
                		"' AND ",
                		SQLQuery.KEY_TYPENAME_ITEMEXP,
                		" = '",
                		typeExpense,
                		"'"),
                null, 
                null, 
                null, 
                null);
    }
    public boolean deleteItemExpenseByAccount(String nameAccount) 
    {
        return db.delete(SQLQuery.DB_ITEM_EXPENSE_TABLE, CommonFunct.join(SQLQuery.KEY_NAMEACCOUNT_ITEMEXP,
				" = '",nameAccount,"'"), null) > 0;
    }   
    public boolean deleteItemExpenseByAccountAndExpense(String typeExpense, String curNameAccount) 
    {
        return db.delete(SQLQuery.DB_ITEM_EXPENSE_TABLE, CommonFunct.join(SQLQuery.KEY_TYPENAME_ITEMEXP,
				" = '",typeExpense,"' AND ",SQLQuery.KEY_NAMEACCOUNT_ITEMEXP, " = '", curNameAccount, "'"), null) > 0;
    }
    //test
    public boolean deleteItemExpenseByCategory(String category) 
    {
        return db.delete(SQLQuery.DB_ITEM_EXPENSE_TABLE, CommonFunct.join(SQLQuery.KEY_TYPENAME_ITEMEXP,
				" = '",category,"'"), null) > 0;
    }
    
}
